﻿<%@ WebHandler Language="C#" Class="ContainerStepRecord" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 批次派报检信息查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:
 **/

public class ContainerStepRecord : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string factoryid = "", workcenterid = "", teamid = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            teamid = selectOrg[2].ToString();
        }
        else if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }
        string sql = @"
SELECT f.factoryname,'报工' datasource,cci.mfgordername,cci.familylistnames,cci.productname || ':' || cci.productrevision productinfo,p.charactertype,
       cci.productdescription,cci.containername,ctr.workcentername,ctr.workflowstepname,
       CASE WHEN s.iskey=1 THEN '是'
            WHEN s.iskey=0 THEN '否'
            ELSE ''
       END iskey,
       ctr.workflowname || ':' || ctr.workflowrevision workflowinfo,
       ctr.dispatchemployeename,ctr.dispatchdate,ctr.dispatchqty,
       ctr.teamname,ctr.dispatchresourcename,e2.fullname receiver,cdt.recvdate receivdate,
       CASE WHEN cdt.recvdate IS NOT NULL AND ctr.reportdate IS NOT NULL THEN ROUND((ctr.reportdate-cdt.recvdate) * 24 * 60,2)
            ELSE NULL 
       END reportwaitdays, 
       ctr.reportemployeename reportemployeename,wk.employeename,ctr.reportdate,ctr.reportqty,
       ctr.checkemployeename,ctr.checkdate,
       NVL(ctr.lossqty,0) lossqty,lrs.lossreasonname,(ctr.reportqty - NVL(ctr.lossqty,0)) hegeqty,
       cdt.plannedstartdate,cdt.plannedfinishdate,ck.fullname tocheck,
       CASE WHEN ctr.checkdate IS NOT NULL THEN '已完工'
            WHEN ctr.checkdate IS NULL AND ctr.reportdate IS NOT NULL THEN '待检验'
            WHEN ctr.reportdate IS NULL THEN '待报工'
       END status,u.notes,ws.makecycle,
       CASE WHEN ctr.reportdate IS NOT NULL AND cdt.recvdate IS NOT NULL 
                 AND (ctr.reportqty * NVL(ws.makecycle,0)) > ROUND((ctr.reportdate-cdt.recvdate) * 24 * 60,2) THEN '连续报工'
            WHEN ctr.reportdate IS NOT NULL AND cdt.recvdate IS NOT NULL 
                 AND (ctr.reportqty * NVL(ws.makecycle,0)) <= ROUND((ctr.reportdate-cdt.recvdate) * 24 * 60,2) THEN '正常报工'
            ELSE NULL 
       END reporttype
FROM containercurrentinfo cci
LEFT JOIN product p ON p.productid=cci.productid
INNER JOIN containersteprecord ctr ON ctr.containerid = cci.containerid
LEFT JOIN spec s ON s.specid=ctr.specid
LEFT JOIN spectype st ON s.spectypeid=st.spectypeid
LEFT JOIN factory f ON f.factoryid=cci.factoryid
LEFT JOIN containerstepdispatch cdt ON cdt.containerstepdispatchid = ctr.dispatchid AND cdt.isuse = 0
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = ctr.containersteprecordid
LEFT JOIN checkqtychangerecord cr ON cr.containerstepworkreportid=csw.containerstepworkreportid AND cr.isuse = 0
LEFT JOIN lossreason lrs ON lrs.lossreasonid = cr.reasonid
LEFT JOIN employee ck ON ck.employeeid = csw.checkerid
LEFT JOIN 
(
  SELECT u.containerid,u.workflowstepid,listagg(u.notes,'、') WITHIN GROUP (ORDER BY u.notes) notes
  FROM containerstepattribute u
  LEFT JOIN containercurrentinfo cci ON cci.containerid = u.containerid
  WHERE u.notes IS NOT NULL";
        // 分厂
        if (factoryid != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        sql += @"
  GROUP BY u.containerid,u.workflowstepid
) u ON u.containerid = ctr.containerid AND u.workflowstepid = ctr.workflowstepid
LEFT JOIN employee wk ON wk.employeeid = ctr.reportemployeeid
LEFT JOIN employee e2 ON e2.employeeid = cdt.recvemployeeid
LEFT JOIN workflowstep ws ON ws.workflowstepid = ctr.workflowstepid
WHERE cci.containerstatus = 1 ";
        // 分厂
        if (factoryid != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        // 工区
        if (workcenterid != "")
        {
            sql += string.Format(" AND ctr.workcenterid = '{0}'", workcenterid);
        }

        // 班组
        if (teamid != "")
        {
            sql += string.Format(" AND ctr.teamid = '{0}'", teamid);
        }

        // 订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername Like '%{0}%'", jObject["mfgordername"].ToString());
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        // 工序号
        if (jObject["specno"] != null && jObject["specno"].ToString() != "")
        {
            sql += string.Format(" AND ctr.specno like '%{0}%'", jObject["specno"].ToString());
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 派工人
        if (jObject["dispatcher"] != null && jObject["dispatcher"].ToString() != "")
        {
            sql += string.Format(" AND ctr.dispatchemployeeid = '{0}'", jObject["dispatcher"].ToString());
        }

        // 领工人
        if (jObject["reveiver"] != null && jObject["reveiver"].ToString() != "")
        {
            sql += string.Format(" AND cdt.recvemployeeid = '{0}'", jObject["reveiver"].ToString());
        }

        // 报工人
        if (jObject["worker"] != null && jObject["worker"].ToString() != "")
        {
            sql += string.Format(" AND ctr.reportemployeeid = '{0}'", jObject["worker"].ToString());
        }

        // 检验人
        if (jObject["checker"] != null && jObject["checker"].ToString() != "")
        {
            sql += string.Format(" AND ctr.checkemployeeid = '{0}'", jObject["checker"].ToString());
        }

        // 派工日期
        if (jObject["dispatchdate"] != null && jObject["dispatchdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["dispatchdate"].ToString());
            sql += string.Format(" AND ctr.dispatchdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND ctr.dispatchdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        // 领工时间
        if (jObject["reveivedate"] != null && jObject["reveivedate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["reveivedate"].ToString());
            sql += string.Format(" AND cdt.recvdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND cdt.recvdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        // 报工日期
        if (jObject["reportdate"] != null && jObject["reportdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["reportdate"].ToString());
            sql += string.Format(" AND ctr.reportdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND ctr.reportdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        // 检验日期
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND ctr.checkdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND ctr.checkdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "待报工")
            {
                sql += " AND ctr.reportdate IS NULL ";
            }
            if (jObject["status"].ToString() == "待检验")
            {
                sql += " AND ctr.reportdate IS NOT NULL AND ctr.checkdate IS NULL ";
            }
            if (jObject["status"].ToString() == "已完工")
            {
                sql += " AND ctr.checkdate IS NOT NULL ";
            }
        }

        if (jObject["spectypename"] != null && jObject["spectypename"].ToString() != "")
        {
            sql += string.Format(" AND st.spectypename like '%{0}%'", jObject["spectypename"].ToString());
        }

        if (jObject["iskey"] != null && jObject["iskey"].ToString() != "")
        {
            sql += string.Format(" AND s.iskey = '{0}'", jObject["iskey"].ToString());
        }

        if (jObject["charactertype"] != null && jObject["charactertype"].ToString() != "")
        {
            sql += string.Format(" AND p.charactertype = '{0}'", jObject["charactertype"].ToString());
        }

        if (jObject["shownotes"] != null && jObject["shownotes"].ToString() == "是")
        {
            sql += @"
UNION ALL

SELECT f.factoryname,'工序备注维护' datasource,cci.mfgordername,cci.familylistnames,cci.productname || ':' || cci.productrevision productinfo,p.charactertype,
       cci.productdescription,cci.containername,NULL workcentername,ws.workflowstepname,
       CASE WHEN s.iskey=1 THEN '是'
            WHEN s.iskey=0 THEN '否'
            ELSE ''
       END iskey,
       wfb.workflowname || ':' || wf.workflowrevision workflowinfo,
       NULL dispatchemployeename,NULL dispatchdate,NULL dispatchqty,
       NULL teamname,NULL dispatchresourcename,NULL receiver,NULL receivdate,
       NULL reportwaitdays, 
       NULL reportemployeename,NULL employeename,NULL reportdate,NULL reportqty,
       NULL checkemployeename,NULL checkdate,
       NULL lossqty,lrs.lossreasonname,NULL hegeqty,
       NULL plannedstartdate,NULL plannedfinishdate,NULL tocheck,
       NULL status,t.notes,NULL makecycle,NULL reporttype
FROM 
(
  SELECT u.containerid,csd.containersteprecordid,u.workflowstepid,listagg(u.notes,'、') WITHIN GROUP (ORDER BY u.notes) notes
  FROM containerstepattribute u
  LEFT JOIN containercurrentinfo cci ON cci.containerid = u.containerid
  LEFT JOIN containersteprecord csd ON csd.containerid = u.containerid AND csd.workflowstepid = u.workflowstepid
  WHERE u.notes IS NOT NULL AND csd.containersteprecordid IS NULL ";

            // 分厂
            if (factoryid != "")
            {
                sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
            }

            // 机型
            if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
            {
                sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
            }

            // 零件图号
            if (jObject["productname"] != null && jObject["productname"].ToString() != "")
            {
                sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
            }

            // 流水卡号
            if (jObject["containername"] != null && jObject["containername"].ToString() != "")
            {
                sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
            }

            sql += @"
  GROUP BY u.containerid,csd.containersteprecordid,u.workflowstepid
) t
LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
LEFT JOIN product p ON p.productid=cci.productid
LEFT JOIN factory f ON f.factoryid=cci.factoryid
LEFT JOIN containersteprecord ctr ON ctr.containersteprecordid = t.containersteprecordid
LEFT JOIN spec s ON s.specid=ctr.specid
LEFT JOIN spectype st ON s.spectypeid=st.spectypeid
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = ctr.containersteprecordid
LEFT JOIN checkqtychangerecord cr ON cr.containerstepworkreportid=csw.containerstepworkreportid AND cr.isuse = 0
LEFT JOIN lossreason lrs ON lrs.lossreasonid = cr.reasonid
LEFT JOIN workflowstep ws ON ws.workflowstepid = t.workflowstepid
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
WHERE 1=1";
            if (jObject["iskey"] != null && jObject["iskey"].ToString() != "")
            {
                sql += string.Format(" AND s.iskey = '{0}'", jObject["iskey"].ToString());
            }

            if (jObject["charactertype"] != null && jObject["charactertype"].ToString() != "")
            {
                sql += string.Format(" AND p.charactertype = '{0}'", jObject["charactertype"].ToString());
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns["mfgordername"].Caption = "订单号";
        dt.Columns["familylistnames"].Caption = "机型";
        dt.Columns["productinfo"].Caption = "零件号及版本";
        dt.Columns["charactertype"].Caption = "特性类型";
        dt.Columns["workflowinfo"].Caption = "工艺及版本";
        dt.Columns["notes"].Caption = "工艺备注";
        dt.Columns["datasource"].Caption = "数据来源";
        dt.Columns["productdescription"].Caption = "零件名称";
        dt.Columns["containername"].Caption = "流水卡号";
        dt.Columns["workcentername"].Caption = "主制工区";
        dt.Columns["workflowstepname"].Caption = "工序";
        dt.Columns["iskey"].Caption = "关键序";
        dt.Columns["dispatchemployeename"].Caption = "派工人";
        dt.Columns["dispatchdate"].Caption = "派工时间";
        dt.Columns["dispatchqty"].Caption = "派工数量";
        dt.Columns["receiver"].Caption = "领工人";
        dt.Columns["receivdate"].Caption = "领工时间";
        dt.Columns["reportwaitdays"].Caption = "领-报间隔分钟数";
        dt.Columns["teamname"].Caption = "班组";
        dt.Columns["dispatchresourcename"].Caption = "设备";
        dt.Columns["reportemployeename"].Caption = "报工人";
        dt.Columns["employeename"].Caption = "报工人员工号";
        dt.Columns["factoryname"].Caption = "分厂";
        dt.Columns["reportdate"].Caption = "报工时间";
        dt.Columns["reportqty"].Caption = "报工数";
        dt.Columns["checkemployeename"].Caption = "检验员";
        dt.Columns["checkdate"].Caption = "检验日期";
        dt.Columns["lossqty"].Caption = "报废数";
        dt.Columns["lossreasonname"].Caption = "报废原因";
        dt.Columns["hegeQty"].Caption = "合格数";
        dt.Columns["plannedstartdate"].Caption = "派工开始日期";
        dt.Columns["plannedfinishdate"].Caption = "要求完成日期";
        dt.Columns["status"].Caption = "状态";
        dt.Columns["tocheck"].Caption = "待检人";
        dt.Columns["makecycle"].Caption = "单件加工时间";
        dt.Columns["reporttype"].Caption = "报工方式";
        ExportHelper.ExportExcel(dt, "批次派报检信息查询报表");
    }
}